﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PM.Entity;
using System.Data;
using System.Data.OracleClient;

namespace PM.Services
{
    public class BankTransactionServices
    {

        private static BankTransaction GetBankTransFromDataReader(IDataReader dataReader)
        {
            BankTransaction retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new BankTransaction();
                retVal.TransactionId = Int32.Parse(dataReader["TRANSACTION_ID"].ToString());
                retVal.AccNo = dataReader["ACC_NO"].ToString();
                retVal.DebitCredit = dataReader["DEBIT_CREDIT"].ToString();                
                decimal amount = 0.0M;
                decimal.TryParse(dataReader["AMOUNT"].ToString(), out amount);
                retVal.Amount = amount;
                DateTime transDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["TRANS_DATE"].ToString(), out transDate))
                {
                    retVal.TransDate = transDate;
                } 
                int posted = 0;
                int.TryParse(dataReader["POSTED"].ToString(), out posted);
                if (posted == 1)
                {
                    retVal.AccPosted = true;
                }
                else
                {
                    retVal.AccPosted = false;
                }              

            }
            return retVal;
        }

        public static BankTransactionCollection GetBankTransCollection()
        {
            BankTransactionCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_TRANSACTION";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new BankTransactionCollection();
                    while (dataReader.Read())
                    {
                        BankTransaction item = GetBankTransFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static BankTransaction GetBankTransactionByTransId(string transId)
        {
            BankTransaction retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_TRANSACTION where TRANSACTION_ID = " + transId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetBankTransFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static BankTransactionCollection GetBankTransactionCollectionByAccNo(string accNo)
        {
            BankTransactionCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK_TRANSACTION where ACC_NO = '" + accNo + "'";
                cmd.Connection = con;
                                
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new BankTransactionCollection();
                    while (dataReader.Read())
                    {
                        BankTransaction item = GetBankTransFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static List<string> GetDebitCreditCollection()
        {
            OracleConnection con = new OracleConnection();
            List<string> retVal = new List<string>();
            retVal.Add("C");
            retVal.Add("D");
            return retVal;
        }

        public static void Add(BankTransaction bt)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "select 1 from BANK_TRANSACTION where TRANSACTION_ID = :v1 ";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bt.TransactionId;
                
                cmd.Connection = con;
                object obj = cmd.ExecuteScalar();
                if (obj != null && int.Parse(obj.ToString()) == 1)
                {
                    Update(bt);
                    return;
                }
                cmd.Parameters.Clear();
                cmd.CommandText = string.Empty;
                cmd.CommandText = "insert into BANK_TRANSACTION(TRANSACTION_ID, ACC_NO, DEBIT_CREDIT, AMOUNT," +
                                                               "TRANS_DATE, POSTED)";

                cmd.CommandText += " values(seq_transaction.nextval,:v2,:v3,:v4,:v5,:v6)";

                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = bt.AccNo;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = bt.DebitCredit;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = bt.Amount;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = bt.TransDate;
                if (bt.AccPosted)
                {
                    cmd.Parameters.Add(":v6", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v6", OracleType.Number).Value = 0;
                }
                cmd.ExecuteNonQuery();
                con.Close();

                if (bt.AccPosted)
                {
                    AccuredInterestServices.CalculateAccruedInterest(bt.AccNo, bt.TransDate);
                    BankDepositServices.ModifyDepositByTransaction(bt.AccNo, bt.DebitCredit, bt.Amount);
                    //AccuredInterestServices.CalculateAccruedInterest(bt.AccNo, DateTime.Today);
                    
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(BankTransaction bt)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update BANK_TRANSACTION set " +
                                  "ACC_NO = :v2, " +
                                  "DEBIT_CREDIT = :v3, " +
                                  "AMOUNT = :v4, " +
                                  "TRANS_DATE = :v5, " +
                                  "POSTED = :v6 ";
                cmd.CommandText += " where TRANSACTION_ID = :v1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bt.TransactionId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = bt.AccNo;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = bt.DebitCredit;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = bt.Amount;               
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = bt.TransDate;           
                
                if (bt.AccPosted)
                {
                    cmd.Parameters.Add(":v6", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v6", OracleType.Number).Value = 0;
                }
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
                con.Close();
                if (bt.AccPosted)
                {
                    AccuredInterestServices.CalculateAccruedInterest(bt.AccNo, bt.TransDate);
                    BankDepositServices.ModifyDepositByTransaction(bt.AccNo, bt.DebitCredit, bt.Amount); 
                    //AccuredInterestServices.CalculateAccruedInterest(bt.AccNo, DateTime.Today);
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }  

}
